package com.group.voting_system.utils;

import com.group.voting_system.entity.User;
import com.group.voting_system.exception.ParamNotValidException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: xyf
 * @Date: 2019/5/4 23:05
 */
public class ExcelUtil {

    //是否符合2003Excel
    public static boolean isExcel2003(String filePath){
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //是否符合2007Excel
    public static boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    public static List<User> readExcel(MultipartFile file){
        Workbook workbook = null;
        List<User> list = new ArrayList<>();
        if (ExcelUtil.isExcel2007(file.getOriginalFilename())){
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (ExcelUtil.isExcel2003(file.getOriginalFilename())){
            try {
                workbook = new HSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        Sheet sheet = workbook.getSheetAt(0);
//        从第一个数据开始读
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++){
            Row row = sheet.getRow(i);
            //获取工号
            Cell jobNumberCell = row.getCell(0);
            jobNumberCell.setCellType(1);
            String jobNumber = jobNumberCell.getStringCellValue();
            //获取姓名
            String username = row.getCell(1).getStringCellValue();
            //获取性别
            String sex = row.getCell(2).getStringCellValue();
            //获取学院
            String academy = row.getCell(3).getStringCellValue();
            if (jobNumber.equals("") || username == null){
                break;
            }
            User user = new User();
            user.setUsername(username);
            user.setJobNumber(jobNumber);
            user.setSex(sex.equals("男"));
            user.setAcademy(academy);
            list.add(user);

        }

        //最后要关闭流
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return list;
    }
}
